Testing Database Applications with SQL Semantics

Testing of database applications is crucial for ensuring high software quality as undetected faults can result in unrecoverable data corruption. The problem of database application testing can be broadly partitioned into the problems of test cases generation, test data preparation and test outcomes verification. Among the three problems, the problem of test cases generation directly affects the effectiveness of testing. Conventionally, database application testing is based upon whether or not the application can perform a set of predefined functions. While it is useful to achieve a basic degree of quality by considering the application to be a black box in the testing process, white box testing is required for more thorough testing. However, the semantics of the Structural Query Language (SQL) statements embedded in database applications are rarely considered in conventional white box testing techniques. In this paper, we propose to complement white box techniques with the inclusion of the SQL semantics. Our approach is to transform the embedded SQL statements to procedures in some general-purpose programming language and thereby generate test cases using conventional white box testing techniques. Additional test cases that are not covered in traditional white box testing are generated to improve the effectiveness of database application testing. The steps of both SQL statements transformation and test cases generation will be explained and illustrated using an example adapted from a course registration system. We successfully identify additional faults involving the internal states of databases.